﻿#include "sqlbase.h"

SqlBase::SqlBase(QObject *parent) : QObject(parent)
{
    type = 0;
    globalData = GlobalData::GetInstance();

    timer = new QTimer;
    connect(timer, SIGNAL(timeout()), this, SLOT(OnTimer()));
    timer->start(5000);

    this->OnTimer();
}

void SqlBase::OnTimer(){
    if(!dataBase.isOpen()){
        this->Open();
    }
}


void SqlBase::Open(){
    dataBase.close();

    QStringList drivers = QSqlDatabase::drivers();//返回所有数据库可用程序列表
    foreach(QString driver,drivers) {
      // qDebug() << "-------------------" << driver;
    }
    if(type == 0){
        QString dataPath = MyHelper::GetFilePath("data/MainTain.db");
        dataBase = QSqlDatabase::addDatabase("QSQLITE", MyHelper::GetUUID());//添加数据库 指定类型和数据库连接名称
        dataBase.setDatabaseName("data/MainTain.db");//指定数据库名称 不同于连接名称
    }else{
        dataBase = QSqlDatabase::addDatabase("QMYSQL", MyHelper::GetUUID());
        dataBase.setHostName(globalData->config->database->ip);//ip
        dataBase.setPort(globalData->config->database->port);//端口号
        dataBase.setUserName(globalData->config->database->userName);//用户名
        dataBase.setPassword(globalData->config->database->password);//密码
        dataBase.setDatabaseName(globalData->config->database->databaseName);//数据库名
    }

    bool open = dataBase.open();
    if(open){
        MyLog::Info("数据库打开成功");
        this->CreateTable();//创建表
        this->InitDefaultData();//初始化表格
    }else{
        MyLog::Error("数据库打开失败：" + dataBase.lastError().text());
        dataBase.close();
    }
    globalData->dataBaseOpen = open;
}

void SqlBase::Close(){
    dataBase.close();
    globalData->dataBaseOpen = false;
    MyLog::Info("数据库关闭");
}

void SqlBase::CreateTable(){
    QStringList tables = dataBase.tables();
    QString sql;

    //创建用户
    if(!tables.contains("sys_user")){
        sql = "create table sys_user(code text primary key, password text, name text, role int, builtin bool)";
        this->ExecSql(sql);
    }

    //创建系统日志
    if(!tables.contains("sys_log")){
        sql = "create table sys_log(id integer primary key, type int, operater text, describe text, createTime datetime)";
        this->ExecSql(sql);
    }

    //创建设备表
    if(!tables.contains("sys_equipment")){
        sql = "create table sys_equipment(devCode text primary key, devid tinyint,devname text, serialName text,baudRate int,dataBit int,checkoutBit int,stopBit int,cosrs float(5,1), cosfs float(5,1),cosgp float(5,1),cos_140 float(5,1), cos_18 float(5,1), cos_12 float(5,1), cost_z18 float(5,1), cos_24 float(5,1), cosyp float(5,1), coszf float(5,1), costz float(5,1), costd float(5,1))";
        //sql = "drop table sys_equipment";
        this->ExecSql(sql);
    }

    //创建指标数据
    if(!tables.contains("data_quality")){
        sql = "create table data_quality(id integer primary key, code text, startTime datetime, endTime datetime, data text)";
        this->ExecSql(sql);
    }

    //创建指标报警
    if(!tables.contains("data_quality_alarm")){
        sql = "create table data_quality_alarm(id integer primary key, code text, type int, startDate datetime, endDate datetime, value real, relieve int, reason text)";
        this->ExecSql(sql);
    }

    //创建DM数据表 hyj
    if(!tables.contains("data_DAM")){
        sql="create table data_DAM(HEAD smallint,LENGTH tinyint,DEVID tinyint,CMD tinyint,DATA tinyint,CRC smallint)";
        this->ExecSql(sql);
    }
    if(!tables.contains("data_State")){
        //sql="drop table data_State";
        sql="create table data_State(devname text, datetime datetime, data text)";
        this->ExecSql(sql);
    }
    if(!tables.contains("data_Sim")){
        sql="create table data_Sim(devname text, data datetime,launch float(5,1),reflect float(5,1),highFreq float(5,1),FOFZ float(5,1),"
            "FOE float(5,1),OT float(5,1),OE float(5,1),TF float(5,1),audio float(5,1),putting float(5,1),modulate float(5,1),drive float(5,1),fre float(5,1))";
    this->ExecSql(sql);
    }
    if(!tables.contains("data_IP")){
        sql="create table data_IP(Kw text,ip text,port int)";
         this->ExecSql(sql);
    }
}

void SqlBase::SqlBase::InitDefaultData(){

    QList<Equipment> equList = this->GetEquipmentList();//获取数据库内容添加到List
    if(equList.size() == 0){
        Equipment bean;
        bean.devCode = "001";
        bean.devid = 0x00;
        bean.devname = "发射机";
        bean.serialName = "COM1";
        bean.baudRate = 9600;
        bean.dataBit = 8;
        bean.checkoutBit = 0;
        bean.stopBit = 0;
       //bean.enable = true;
        bool a=this->SaveEquipment(bean);//给数据表添加内容
        Equipment bean1;
        bean1.devCode = "002";
        bean1.devid = 0x01;
        bean1.devname = "备用发射机";
        bean1.serialName = "COM3";
        bean1.baudRate = 9600;
        bean1.dataBit = 8;
        bean1.checkoutBit = 0;
        bean1.stopBit = 0;
       //bean.enable = true;
        a=this->SaveEquipment(bean1);//给数据表添加内容
#if 0
        Equipment bean2;
        bean2.devCode = "003";
        bean2.devid = 0x02;
        bean2.devname = "中央一套1号";
        bean2.type = 0;
        bean2.cosjx = 20;
        bean2.serialName = "COM5";
        bean2.baudRate = 9600;
        bean2.dataBit = 8;
        bean2.checkoutBit = 0;
        bean2.stopBit = 0;
        bean2.cosfs = 10;
        bean2.cosdl = 100;
        bean2.cosdy = 300;
        bean2.costx = 10;
        bean2.cosdt = 10;
        bean2.costfd = 150;
        bean2.cos22 = 50;
        bean2.cos22_ = 50;
        bean2.cos8 = 20;
        bean2.cos8_ = 20;
        bean2.cossp = 10;

       //bean.enable = true;
        a=this->SaveEquipment(bean2);//给数据表添加内容
        Equipment bean3;
        bean3.devCode = "004";
        bean3.devid = 0x03;
        bean3.devname = "中央一套2号";
        bean3.type = 0;
        bean3.cosjx = 20;
        bean3.serialName = "COM7";
        bean3.baudRate = 9600;
        bean3.dataBit = 8;
        bean3.checkoutBit = 0;
        bean3.stopBit = 0;
        bean3.cosfs = 10;
        bean3.cosdl = 100;
        bean3.cosdy = 300;
        bean3.costx = 10;
        bean3.cosdt = 10;
        bean3.costfd = 150;
        bean3.cos22 = 50;
        bean3.cos22_ = 50;
        bean3.cos8 = 20;
        bean3.cos8_ = 20;
        bean3.cossp = 10;
       //bean.enable = true;
        a=this->SaveEquipment(bean3);//给数据表添加内容
#endif
    }

    QList<IPData> IpList=this->GetIpList();
    if(IpList.size()==0)
    {
        IPData data1;
        data1.Kw=QString("50Kw");
        data1.IP=QString("192.168.0.254");
        data1.Port=8021;
        bool b=this->SaveIPData(data1);
        IPData data2;
        data2.Kw=QString("10Kw");
        data2.IP=QString("192.168.0.255");
        data2.Port=8022;
        b=this->SaveIPData(data2);
    }

//    QList<FrameFormat> forList = this->GetAgreementList();//获取数据库内容添加到List
//    if(forList.size() == 0){
//        FrameFormat bean;
//        bean.HEAD = 0x7EE7;
//        bean.LENGTH = 0x04;
//        bean.DEVID = 0x01;
//        bean.CMD = 0x00;
//        bean.DATA[0] = 0x01;
//        bean.CRC = 0x00;
//        this->SaveAgreement(bean);//给数据表添加内容
//    }


    //初始化用户
    QList<User> userList = this->GetUserList();
    if(userList.size() == 0){
        User bean;
        bean.code = "admin";
        bean.password = "admin";
        bean.name = "管理员";
        bean.role = 0;
        bean.builtin = true;
        this->SaveUser(bean);
    }
}

void SqlBase::StopAllAlarm(){
    QString sql = QString("update %1 set relieve = '1', endDate = " + this->ToDateTime(QDateTime::currentDateTime()) + " where relieve = 0");
    this->ExecSql(sql.arg("data_quality_alarm"));
}

QSqlQuery SqlBase::Query(QString sql){
    QSqlQuery query(dataBase);
    if(!query.exec(sql)){
        MyLog::Error("SQl查询失败：" + dataBase.lastError().text() + " " + sql + " " + sql);
    }
    return query;
}

bool SqlBase::ExecSql(QString sql){
    QSqlQuery query(dataBase); //QSqlQuery类提供了执行和操作SQL语句的方法。
    if(query.exec(sql)){
        return true;
    }else{
        MyLog::Error("SQl执行失败：" + dataBase.lastError().text() + " " + sql);
        return false;
    }
}

bool SqlBase::DeleteData(QString table, QString key, QString value){
    QString sql = QString("delete from %1 where %2='%3'")
                    .arg(table).arg(key).arg(value);

    bool b = this->ExecSql(sql);
    if(b){
        MyLog::Info(QString("删除成功: %1 %2=%3").arg(table).arg(key).arg(value));
    }
    return b;
}



QString SqlBase::ToTime(QTime time){
    QString str = QString("'%1'").arg(time.toString("HH:mm:ss"));
    if(this->type == 1){
        str = QString("TO_DATE('%1', 'HH24:MI:SS')").arg(time.toString("HH:mm:ss"));
    }
    return str;
}
QString SqlBase::ToData(QDate date){
    QString str = QString("'%1'").arg(date.toString("yyyy-MM-dd"));
    if(this->type == 1){
        str = QString("TO_DATE('%1', 'SYYYY-MM-DD')").arg(date.toString("yyyy-MM-dd"));
    }
    return str;
}
QString SqlBase::ToDateTime(QDateTime dateTime){
    QString str = QString("'%1'").arg(dateTime.toString("yyyy-MM-dd HH:mm:ss"));
    if(this->type == 1){
        str = QString("TO_DATE('%1', 'SYYYY-MM-DD HH24:MI:SS')").arg(dateTime.toString("yyyy-MM-dd HH:mm:ss"));
    }
    return str;
}

//---------------------------------用户---------------------------------------

bool SqlBase::SaveUser(User bean){
    this->DelUser(bean.code);
    QString sql = QString("insert into sys_user (code, password, name, role, builtin) values ('%1', '%2', '%3', '%4', '%5')");
    sql = sql.arg(bean.code).arg(bean.password).arg(bean.name).arg(bean.role).arg(bean.builtin);
    return this->ExecSql(sql);
}


bool SqlBase::DelUser(QString id){
    return this->DeleteData("sys_user", "code", id);
}

QList<User> SqlBase::GetUserList(){
    QList<User> list;
    QString sql = "select * from sys_user order by code asc";

    QSqlQuery query(dataBase);
    if(query.exec(sql)){
        while(query.next()){
            User info;
            info.code = query.value("code").toString();
            info.password = query.value("password").toString();
            info.name = query.value("name").toString();
            info.role = query.value("role").toInt();
            info.builtin = query.value("builtin").toBool();

            list.append(info);
        }
    }else{
        MyLog::Error("SQl查询失败：" + dataBase.lastError().text() + " " + sql);
    }
    return list;
}

//---------------------------------系统日志---------------------------------------

bool SqlBase::SaveSysLog(SysLog bean){
    QString sql = QString("insert into sys_log (type, operater, describe, createTime) values ('%1', '%2', '%3', %4)");
    sql = sql.arg(bean.type).arg(bean.operater).arg(bean.describe).arg(this->ToDateTime(bean.createTime));
    return this->ExecSql(sql);
}

bool SqlBase::DelSysLog(int id){
    return this->DeleteData("sys_log", "id", QString::number(id));
}

bool SqlBase::DelSysLog(QDateTime dateTime){
    QString sql = QString("delete from sys_log where createTime<=%1").arg(this->ToDateTime(dateTime));
    bool b = this->ExecSql(sql);
    if(b){
        MyLog::Info(QString("删除成功: %1").arg(sql));
    }
    return b;
}

QList<SysLog> SqlBase::GetSysLogList(SysLog bean){

    QString where = QString(" where 1=1 ");
    if(bean.type >= 0)
        where += QString(" and type = '%1' ").arg(bean.type);
    if(!bean.operater.isEmpty())
        where += QString(" and operater = '%1' ").arg(bean.operater);
    if(!bean.describe.isEmpty())
        where += QString(" and (operater like '%%1%' or describe like '%%2%') ").arg(bean.describe).arg(bean.describe);
    if(!bean.createTime.isNull()){
        QDateTime st = bean.createTime; st.setTime(QTime(0,0,0));
        QDateTime et = bean.createTime; et.setTime(QTime(23,59,59));
        where += QString(" and createTime >= %1 and createTime <= %2 ").arg(this->ToDateTime(st)).arg(this->ToDateTime(et));
    }

    QString sql = QString("select * from sys_log %1 order by createTime desc").arg(where);

    QList<SysLog> list;
    QSqlQuery query(dataBase);
    if(query.exec(sql)){
        while(query.next()){
            SysLog info;
            info.id = query.value("id").toInt();
            info.type = query.value("type").toInt();
            info.operater = query.value("operater").toString();
            info.describe = query.value("describe").toString();
            info.createTime = query.value("createTime").toDateTime();
            list.append(info);
        }
    }else{
        MyLog::Error("SQl查询失败：" + dataBase.lastError().text() + " " + sql);
    }
    return list;
}
QList<State> SqlBase::GetStateList(QString name, QDateTime DATE1 , QDateTime DATE2)
{
        QString where=QString(" where 1=1 ");
        QDateTime st = DATE1;
        QDateTime et = DATE2;
        QString str=st.toString("yyyy-MM-dd HH:mm:ss");
        QString etr=et.toString("yyyy-MM-dd HH:mm:ss");
        QDateTime st1=QDateTime::fromString(str,"yyyy-MM-dd HH:mm:ss");
        QDateTime et1=QDateTime::fromString(etr,"yyyy-MM-dd HH:mm:ss");
        where += QString(" and devname = '%1' ").arg(name);
        where += QString(" and datetime >= %1 and datetime <= %2 ").arg(this->ToDateTime(st1)).arg(this->ToDateTime(et1));
        QString sql = QString("select * from data_State %1 order by datetime desc").arg(where);

        QList<State> list;
        QSqlQuery query(dataBase);
        if(query.exec(sql)){
            while(query.next()){
                State info;
                //devname, data, NoOne, Notwo, NoThree, NoFour, NoFive, NoSix
                info.devname = query.value("devname").toString();
                info.datetime = query.value("datetime").toDateTime();
                info.data = query.value("data").toString();
                list.append(info);
            }
        }else{
            MyLog::Error("SQl查询失败：" + dataBase.lastError().text() + " " + sql);
        }
        return list;
}

QList<Simulation> SqlBase::GetSimList(QString name, QDateTime DATE1, QDateTime DATE2)
{
    QString where=QString(" where 1=1 ");
    QDateTime st = DATE1;
    QDateTime et = DATE2;
    QString str=st.toString("yyyy-MM-dd HH:mm:ss");
    QString etr=et.toString("yyyy-MM-dd HH:mm:ss");
    QDateTime st1=QDateTime::fromString(str,"yyyy-MM-dd HH:mm:ss");
    QDateTime et1=QDateTime::fromString(etr,"yyyy-MM-dd HH:mm:ss");
    where += QString(" and devname = '%1' ").arg(name);
    where += QString(" and data >= %1 and data <= %2 ").arg(this->ToDateTime(st1)).arg(this->ToDateTime(et1));
    QString sql = QString("select * from data_Sim %1 order by data desc").arg(where);

    QList<Simulation> list;
    QSqlQuery query(dataBase);
    if(query.exec(sql)){
        while(query.next()){
            Simulation info;
            info.devname=query.value("devname").toString();
            info.datetime=query.value("data").toDateTime();
            info.launch=query.value("launch").toFloat();
            info.reflect=query.value("reflect").toFloat();
            info.highFreq=query.value("highFreq").toFloat();
            info.FOFZ=query.value("FOFZ").toFloat();
            info.FOE=query.value("FOE").toFloat();
            info.OT=query.value("OT").toFloat();
            info.OE=query.value("OE").toFloat();
            info.TF=query.value("TF").toFloat();
            info.audio=query.value("audio").toFloat();
            info.putting=query.value("putting").toFloat();
            info.modulate=query.value("modulate").toFloat();
            info.drive=query.value("drive").toFloat();
            info.fre=query.value("fre").toFloat();
            list.append(info);
        }
    }else{
        MyLog::Error("SQl查询失败：" + dataBase.lastError().text() + " " + sql);
    }
    return list;
}


//---------------------------------接收机设备---------------------------------------

bool SqlBase::SaveEquipment(Equipment bean){//给数据表添加内容
    //sql = "create table sys_equipment(devCode text primary key, devid tinyint,devname text, serialName text,baudRate int,dataBit int,checkoutBit int,stopBit int,cosrs float(5,1), cosfs float(5,1),cosgp float(5,1),cos_140 float(5,1), cos_18 float(5,1), cos_12 float(5,1), cost_z18 float(5,1), cos_24 float(5,1), cosyp float(5,1), coszf float(5,1), costz float(5,1), costd float(5,1))";
    //QString sql = QString("insert into sys_equipment (devCode,devid,devname,serialName,baudRate,dataBit,checkoutBit,stopBit,cosrs,cosfs,cosgp,cos_140,cos_18,cos_12,cos_z18,cos_24,cosyp,coszf,costz,costd) values ('%1', '%2', '%3', '%4', '%5', '%6', '%7','%8','%9','%10','%11','%12','%13','%14','%15','%16','%17','%18','%19','%20')");
      QString sql = QString("insert into sys_equipment(devCode, devid ,devname, serialName, baudRate, dataBit, checkoutBit, stopBit, cosrs, cosfs, cosgp, cos_140, cos_18, cos_12, cost_z18, cos_24, cosyp, coszf, costz, costd) values ('%1','%2','%3','%4','%5','%6','%7','%8','%9','%10','%11','%12','%13','%14','%15','%16','%17','%18','%19','%20')");
    sql = sql.arg(bean.devCode).arg(bean.devid).arg(bean.devname).arg(bean.serialName).arg(bean.baudRate).arg(bean.dataBit).arg(bean.checkoutBit).arg(bean.stopBit).arg(bean.cosrs).arg(bean.cosfs).arg(bean.cosgp).arg(bean.cos_140).arg(bean.cos_18).arg(bean.cos_12).arg(bean.cos_z18).arg(bean.cos_24).arg(bean.cosyp).arg(bean.coszf).arg(bean.costz).arg(bean.costd);

    return this->ExecSql(sql);
}

bool SqlBase::UpdateEquipment(Equipment bean){//更新表格
    QString sql = QString("update sys_equipment set  devCode = '%1', devid = '%2', devname = '%3',baudRate = '%4', dataBit = '%5', checkoutBit = '%6', stopBit = '%7', cosrs = '%8',cosfs = '%9', cosgp = '%10',cos_140 = '%11', cos_18 = '%12', cos_12 = '%13', cost_z18 = '%14', cos_24 ='%15', cosyp = '%16', coszf = '%17', costz = '%18', costd = '%19' where serialName='%20'");
    sql = sql.arg(bean.devCode).arg(bean.devid).arg(bean.devname).arg(bean.baudRate).arg(bean.dataBit).arg(bean.checkoutBit).arg(bean.stopBit).arg(bean.cosrs).arg(bean.cosfs).arg(bean.cosgp).arg(bean.cos_140).arg(bean.cos_18).arg(bean.cos_12).arg(bean.cos_z18).arg(bean.cos_24).arg(bean.cosyp).arg(bean.coszf).arg(bean.costz).arg(bean.costd).arg(bean.serialName);
    return this->ExecSql(sql);
}

bool SqlBase::DelEquipment(QString id){
    return this->DeleteData("sys_equipment", "serialName", id);
}

QList<Equipment> SqlBase::GetEquipmentList(){
    QList<Equipment> list;
    QString sql = "select * from sys_equipment order by devCode asc";
    //QString sql = "select * from sys_equipment";

    QSqlQuery query(dataBase);
    if(query.exec(sql)){
        while(query.next()){
            Equipment info;
            info.devCode = query.value("devCode").toString();
            info.devid = query.value("devid").toUInt();
            info.devname = query.value("devname").toString();
            info.serialName = query.value("serialName").toString();
            info.baudRate = query.value("baudRate").toInt();
            info.dataBit = query.value("dataBit").toInt();
            info.checkoutBit = query.value("checkoutBit").toInt();
            info.stopBit = query.value("stopBit").toInt();
            info.cosrs = query.value("cosrs").toFloat();
            info.cosfs = query.value("cosfs").toFloat();
            info.cosgp = query.value("cosgp").toFloat();
            info.cos_140 = query.value("cos_140").toFloat();
            info.cos_18 = query.value("cos_18").toFloat();
            info.cos_12 = query.value("cos_12").toFloat();
            info.cos_z18 = query.value("cos_z18").toFloat();
            info.cos_24 = query.value("cos_24").toFloat();
            info.cosyp = query.value("cosyp").toFloat();
            info.coszf = query.value("coszf").toFloat();
            info.costz = query.value("costz").toFloat();
            info.costd = query.value("costd").toFloat();
            //info.enable = query.value("enable").toBool();

            list.append(info);
        }
    }else{
        MyLog::Error("SQl查询失败：" + dataBase.lastError().text() + " " + sql);
    }
    return list;
}

QMap<QString, Equipment> SqlBase::GetEquipmentMap(){
    QMap<QString, Equipment> map;
    QList<Equipment> list = this->GetEquipmentList();
    for(Equipment info : list){
        map.insert(info.serialName, info);
    }
    return map;
}

QList<IPData> SqlBase::GetIpList()
{
    QList<IPData> list;
    QString sql = "select * from data_IP";

    QSqlQuery query(dataBase);
    if(query.exec(sql)){
        while(query.next()){
            IPData info;
            info.IP=query.value("ip").toString();
            info.Kw=query.value("Kw").toString();
            info.Port=query.value("port").toInt();

            list.append(info);
        }
    }else{
        MyLog::Error("SQl查询失败：" + dataBase.lastError().text() + " " + sql);
    }
    return list;
}

bool SqlBase::SaveIPData(IPData bean)
{
    QString sql = QString("insert into data_IP (Kw,ip,port) values ('%1', '%2', '%3')");
    sql = sql.arg(bean.Kw).arg(bean.IP).arg(bean.Port);

    return this->ExecSql(sql);
}

bool SqlBase::UpdateIPData(IPData bean)
{
    QString sql = QString("update data_IP set ip = '%1', port = '%2' where Kw='%3'");
    sql = sql.arg(bean.IP).arg(bean.Port).arg(bean.Kw);

    return this->ExecSql(sql);
}

bool SqlBase::DelIPData(QString IP)
{
    return this->DeleteData("data_IP", "ip", IP);
}

QMap<QString, IPData> SqlBase::GetIpDataMap()
{
    QMap<QString, IPData> map;
    QList<IPData> list = this->GetIpList();
    for(IPData info : list){
        map.insert(info.IP, info);
    }
    return map;
}

//QMap<QString, Simulation> GetSimulationMap(){
//    QMap<QString, Simulation> map;
//    QList<Simulation> list = this->GetEquipmentList();
//    for(Simulation info : list){
//        map.insert(info.serialName, info);
//    }
//    return map;
//}

bool SqlBase::SaveAgreement(FrameFormat format)
{
    QString sql = QString("INSERT INTO data_DAM (HEAD,LENGTH,DEVID,CMD,DATA,CRC) VALUES ('%1','%2','%3','%4','%5','%6')");
    sql = sql.arg(format.HEAD).arg(format.LENGTH).arg(format.DEVID).arg(format.CMD).arg(format.DATA[0]).arg(format.CRC);
    return this->ExecSql(sql);
}

bool SqlBase::UpdateAgreement(FrameFormat format)
{
    QString sql = QString("UPDATE data_DAM SET HEAD='%1',LENTH='%2',DEVID='%3',CMD='%4',DATA='%5',CRC='%6'");
    sql = sql.arg(format.HEAD).arg(format.LENGTH).arg(format.DEVID).arg(format.CMD).arg(format.DATA[20]).arg(format.CRC);
    return this->ExecSql(sql);
}

bool SqlBase::DelAgreement(FrameFormat code)
{
return true;
}

QList<FrameFormat> SqlBase::GetAgreementList()
{
    QList<FrameFormat> list;
    QString sql = "select * from data_DAM";

    QSqlQuery query(dataBase);
    if(query.exec(sql)){
        while(query.next()){
            FrameFormat info;
            info.HEAD = query.value("HEAD").toUInt();
            info.LENGTH = query.value("LENGTH").toUInt();
            info.DEVID = query.value("DEVID").toUInt();
            info.CMD = query.value("CMD").toUInt();
            info.DATA[0] = query.value("DATA").toUInt();
            info.CRC = query.value("CRC").toUInt();
            list.append(info);
        }
    }else{
        MyLog::Error("SQl查询失败：" + dataBase.lastError().text() + " " + sql);
    }
    return list;
}

//--------------------------------指标测量结果---------------------------------------

bool SqlBase::SaveQuality(Quality bean){
    QString sql;
    sql = QString("insert into data_quality (code, startTime, endTime, data) values ('%1', %2, %3, '%4')");
    sql = sql.arg(bean.code).arg(this->ToDateTime(bean.startTime)).arg(this->ToDateTime(bean.endTime)).arg(bean.data);
    //qDebug()<<"sql"<<sql;
    return this->ExecSql(sql);
}
bool SqlBase::Savedata_State(State * state,QDateTime date){
    QString sql;
    sql= QString("insert into data_State (devname, datetime, data) values ('%1', %2, '%3')");
    sql = sql.arg(state->devname).arg(this->ToDateTime(date)).arg(state->data);
    return this->ExecSql(sql);
}
bool SqlBase::Savedata_Sim(Simulation *sim, QDateTime data)
{
    QString sql;
    sql=QString("insert into data_Sim(devname,data,launch,reflect,highFreq,FOFZ,"
                "FOE,OT,OE,TF,audio,putting,modulate,drive,fre) "
                "values('%1', %2, %3, %4, %5, %6, %7, %8, %9, %10, %11, %12, %13, %14, %15)");
    sql=sql.arg(sim->devname).arg(this->ToDateTime(data)).arg(sim->launch).arg(sim->reflect).arg(sim->highFreq)
            .arg(sim->FOFZ).arg(sim->FOE).arg(sim->OT).arg(sim->OE).arg(sim->TF)
            .arg(sim->audio).arg(sim->putting).arg(sim->modulate).arg(sim->drive).arg(sim->fre);
    return this->ExecSql(sql);
}


bool SqlBase::DelQuality(long id){
    return this->DeleteData("data_quality", "id", QString::number(id));
}

bool SqlBase::DelQuality(QDateTime dateTime){
    QString sql = QString("delete from data_quality where startTime<=%1").arg(this->ToDateTime(dateTime));
    bool b = this->ExecSql(sql);
    if(b){
        MyLog::Info(QString("删除成功: %1").arg(sql));
    }
    return b;
}
bool SqlBase::Deldata_State(QDateTime dateTime){
    QString sql = QString("delete from data_State where datetime<=%1").arg(this->ToDateTime(dateTime));
    bool b = this->ExecSql(sql);
    if(b){
        MyLog::Info(QString("删除成功: %1").arg(sql));
    }
    return b;
}

bool SqlBase::Deldata_Sim(QDateTime dateTime)
{
    QString sql = QString("delete from data_Sim where data<=%1").arg(this->ToDateTime(dateTime));
    bool b = this->ExecSql(sql);
    if(b){
        MyLog::Info(QString("删除成功: %1").arg(sql));
    }
    return b;
}



QList<Quality> SqlBase::GetQualityList(Quality bean){
   // QMap<QString, Equipment> equMap = this->GetEquipmentMap();

    QList<Quality> list;

    QString where = QString(" where 1=1 ");
    if(!bean.code.isEmpty())
        where += QString(" and code = '%1' ").arg(bean.code);
    if(!bean.startTime.isNull()){
        where += QString(" and endTime > %1 ").arg(this->ToDateTime(bean.startTime));
    }
    if(!bean.endTime.isNull()){
        where += QString(" and startTime <= %1").arg(this->ToDateTime(bean.endTime));
    }

    QString sql = QString("select * from data_quality %1 order by startTime asc").arg(where);

    QSqlQuery query(dataBase);
    if(query.exec(sql)){
        while(query.next()){
            Quality info;

            //info.id = query.value("id").toInt();
            info.code = query.value("code").toString();
            info.startTime = query.value("startTime").toDateTime();
            info.endTime = query.value("endTime").toDateTime();
            info.data = query.value("data").toString();

            //info.equ = equMap.value(info.code);
            list.append(info);
        }
    }else{
        MyLog::Error("SQl查询失败：" + dataBase.lastError().text() + " " + sql);
    }

    return list;
}

//---------------------------------指标报警---------------------------------------

bool SqlBase::SaveQualityAlarm(QualityAlarm bean){
    QString sql = QString("insert into data_quality_alarm (code, type, startDate, endDate, value, relieve, reason) values ('%1', '%2', %3, %4, '%5', '%6', '%7')");
    sql = sql.arg(bean.code).arg(bean.type).arg(this->ToDateTime(bean.startDate)).arg(this->ToDateTime(bean.endDate)).arg(bean.value).arg(bean.relieve).arg(bean.reason);
    return this->ExecSql(sql);
}

bool SqlBase::UpdateQualityAlarmRelieve(QualityAlarm bean){
    QString sql = QString("update data_quality_alarm set relieve = '%1', endDate = %2 where id = '%3'");
    sql = sql.arg(bean.relieve).arg(this->ToDateTime(bean.endDate)).arg(bean.id);
    return this->ExecSql(sql);
}

bool SqlBase::DelQualityAlarm(long id){
    return this->DeleteData("data_quality_alarm", "id", QString::number(id));
}

QList<QualityAlarm> SqlBase::GetQualityAlarmList(QualityAlarm bean){

    QMap<QString, Equipment> equMap = this->GetEquipmentMap();

    QList<QualityAlarm> list;
#if 0
    QString where = QString(" where 1=1 ");
    if(!bean.code.isEmpty())
        where += QString(" and code = '%1' ").arg(bean.code);
    if(bean.type >= 0)
        where += QString(" and type = '%1' ").arg(bean.type);

    if(!bean.startDate.isNull()){
        QDateTime st = bean.startDate; st.setTime(QTime(0,0,0));
        QDateTime et = bean.startDate; et.setTime(QTime(23,59,59));
        where += QString(" and ( endDate > %1 or endDate is null ) ").arg(this->ToDateTime(st));
        where += QString(" and startDate <= %1").arg(this->ToDateTime(et));
    }

    QString sql = QString("select * from data_quality_alarm %1 order by startDate desc").arg(where);

    QSqlQuery query(dataBase);
    if(query.exec(sql)){
        while(query.next()){
            QualityAlarm info;

            info.id = query.value("id").toInt();
            info.code = query.value("code").toString();
            info.type = query.value("type").toInt();
            info.startDate = query.value("startDate").toDateTime();
            info.endDate = query.value("endDate").toDateTime();
            info.value = query.value("value").toFloat();
            info.relieve = query.value("relieve").toInt();
            info.reason = query.value("reason").toString();

            info.equ = equMap.value(info.code);
            list.append(info);
        }
    }else{
        MyLog::Error("SQl查询失败：" + dataBase.lastError().text() + " " + sql);
    }
    #endif
    return list;

}

